package com.softwareengineering.trafficapplicationv2;
import java.util.ArrayList;
import java.util.List;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * This file is part of the Traffic Simulation Application.
 * The Traffic Simulation Application is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * 
 * The Traffic Simulation Application is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * You should have received a copy of the GNU General Public License
 * along with the Traffic Simulation Application.  If not, see <http://www.gnu.org/licenses/>.
 * 
 * @file DatabaseHandler.java
 * @author David Black, Chris Nelson, and Garrett Stibbs, (c) December 03, 2012
 * @description Extends SQLiteOpenHelper to manage operations on the
 * 				local database "scoresManager" containing the tables
 * 				"submit" and "high". The database is used for local
 * 				storage of yet-to-submit games and for keeping record
 * 				of local-to-user high scores and configurations.
 * 
 */

public class DatabaseHandler extends SQLiteOpenHelper implements Constants {

	private static final int DATABASE_VERSION = 1;	// database version (increment to trigger onUpgrade)

	private static final String DATABASE_NAME = "scoresManager";	// database name
	private static final String TABLE_SUBMIT = "submit";	// yet-to-submit games table name
	private static final String TABLE_HIGH = "high";	// local-to-user high score games table name

	private static final String KEY_ID = "id";	// handle to primary key
	private static final String KEY_LEVEL = "level";	// handle to level code (corresponding to "Constants.java")
	private static final String KEY_P_FACTOR = "p_factor";	// handle to politeness factor
	private static final String KEY_V0_LIMIT = "v0_limit";	// handle to speed limit
	private static final String KEY_SCORE = "score";	// handle to score

	//	###################
	//		Constructor
	//	###################
	
	public DatabaseHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);	// call super
	}

	//	######################
	//		Insert Methods
	//	######################
	
	// inserts a new high score result to the local-to-user high score games table
	public void addHighScore(SQLiteDatabase db, Score score) {
		ContentValues values = new ContentValues();	// create new ContentValues object to hold associations
		values.put(KEY_LEVEL, score.getLevel());	// put level code (corresponding to "Constants.java")
		values.put(KEY_P_FACTOR, "" + score.getP_factor());	// put politeness factor
		values.put(KEY_V0_LIMIT, "" + score.getV0_limit());	// put speed limit
		values.put(KEY_SCORE, score.getScore());	// put score

		db.insert(TABLE_HIGH, null, values);	// insert new row in table
	}

	// inserts a new score result to the yet-to-submit games table
	public void addScore(Score score) {
		SQLiteDatabase db = this.getWritableDatabase();	// get handle to WritableDatabase

		ContentValues values = new ContentValues();	// create new ContentValues object to hold associations
		values.put(KEY_LEVEL, score.getLevel());	// put level code (corresponding to "Constants.java")
		values.put(KEY_P_FACTOR, "" + score.getP_factor());	// put politeness factor
		values.put(KEY_V0_LIMIT, "" + score.getV0_limit());	// put speed limit
		values.put(KEY_SCORE, score.getScore());	// put score

		db.insert(TABLE_SUBMIT, null, values);	// insert new row in table
		db.close();	// close database

		updateScore(score);	// attempt to update local-to-user high score
	}

	//	######################
	//		Delete Methods
	//	######################
	
	// delete all score results from the yet-to-submit games table
	public void deleteScores() {
		SQLiteDatabase db = this.getWritableDatabase();	// get handle to WritableDatabase
		db.delete(TABLE_SUBMIT, null, null);	// clear table
		db.close();	// close database
	}
	
	//	###############
	//		Getters
	//	###############

	// get list of high score results from local-to-user high score games table
	public List<Score> getHighScores() {
		List<Score> scoreList = new ArrayList<Score>();	// create new list of Score objects

		String selectQuery = "SELECT * FROM " + TABLE_HIGH;	// specify query
		SQLiteDatabase db = this.getReadableDatabase();	// get handle to ReadableDatabase
		Cursor cursor = db.rawQuery(selectQuery, null);	// run query and put results in Cursor object

		if (cursor.moveToFirst()) {	// if results returned
			do {
				Score score = new Score();	// create new Score object to store row configuration
				score.setLevel(cursor.getInt(1));	// put level code (corresponding to "Constants.java")
				score.setP_factor(Double.parseDouble(cursor.getString(2)));	// put politeness factor
				score.setV0_limit(Double.parseDouble(cursor.getString(3)));	// put speed limit
				score.setScore(cursor.getInt(4));	// put score
				scoreList.add(score);	// add Score object to list
			} while (cursor.moveToNext());	// loop for all rows in Cursor
		}

		cursor.close();	// close cursor
		db.close();	// close database
		return scoreList;	// return list of Score objects
	}

	// get JSONArray String of score results from yet-to-submit games table
	public String getSubmitScores() {
		JSONArray rows = new JSONArray();	// create new JSONArray to store results

		String selectQuery = "SELECT * FROM " + TABLE_SUBMIT;	// specify query
		SQLiteDatabase db = this.getReadableDatabase();	// get handle to ReadableDatabase
		Cursor cursor = db.rawQuery(selectQuery, null);	// run query and put results in Cursor object

		if (cursor.moveToFirst()) {	// if results returned
			do {
				try {
					JSONObject row = new JSONObject();	// create new JSONObject to store row
					row.put("level", cursor.getInt(1));	// put level code (corresponding to "Constants.java")
					row.put("p_factor", Double.parseDouble(cursor.getString(2)));	// put politeness factor
					row.put("v0_limit", Double.parseDouble(cursor.getString(3)));	// put speed limit
					row.put("score", cursor.getInt(4));	// put score
					rows.put(row);	// add JSONObject to JSONArray
				} catch (JSONException e) {	// if JSONException
					cursor.close();	// close cursor
					db.close();	// close database
					return null;	// return null
				}
			} while (cursor.moveToNext());	// loop for all rows in Cursor
		}

		cursor.close();	// close cursor
		db.close();	// close database
		return rows.toString();	// return JSONArray.toString()
	}
	
	//	#################
	//		Listeners
	//	#################

	@Override	// handles onCreate event
	public void onCreate(SQLiteDatabase db) {
		// specify SQL String for creation of yet-to-submit table
		String CREATE_SUBMIT_TABLE = "CREATE TABLE " + TABLE_SUBMIT + "("
				+ KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_LEVEL
				+ " INTEGER," + KEY_P_FACTOR + " TEXT," + KEY_V0_LIMIT
				+ " TEXT," + KEY_SCORE + " INTEGER)";

		// specify SQL String for creation of local-to-user high score table
		String CREATE_HIGH_TABLE = "CREATE TABLE " + TABLE_HIGH + "(" + KEY_ID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_LEVEL
				+ " INTEGER," + KEY_P_FACTOR + " TEXT," + KEY_V0_LIMIT
				+ " TEXT," + KEY_SCORE + " INTEGER)";

		db.execSQL(CREATE_SUBMIT_TABLE);	// create yet-to-submit table
		db.execSQL(CREATE_HIGH_TABLE);	// create local-to-user high score table

		// populate local-to-user high score table with rows for all level configurations
		addHighScore(db, new Score(ON_RAMP, 0, 0, 0));	// insert ON_RAMP level row
		addHighScore(db, new Score(LANE_CLOSURE, 0, 0, 0));	// insert LANE_CLOSURE level row
		addHighScore(db, new Score(UPHILL, 0, 0, 0));	// insert UPHILL level row
		addHighScore(db, new Score(TRAFFIC_LIGHT, 0, 0, 0));	// insert TRAFFIC_LIGHT level row
		addHighScore(db, new Score(FOUR_WAY,0,0,0)); // insert FOUR_WAY level row
	}

	@Override	// handles onUpgrade event
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_SUBMIT);	// drop existing yet-to-submit table
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_HIGH);	// drop existing local-to-user high score table

		onCreate(db);	// call onCreate to recreate tables
	}
	
	//	######################
	//		Update Methods
	//	######################

	// attempt to update the local-to-user high score games table
	public void updateScore(Score score) {
		SQLiteDatabase db = this.getWritableDatabase();	// get handle to WritableDatabase

		ContentValues values = new ContentValues();	// create new ContentValues object to hold associations
		values.put(KEY_P_FACTOR, "" + score.getP_factor());	// put politeness factor
		values.put(KEY_V0_LIMIT, "" + score.getV0_limit());	// put speed limit
		values.put(KEY_SCORE, score.getScore());	// put score

		// update table row for appropriate level if new score > previous high score
		db.update(TABLE_HIGH, values, KEY_LEVEL + " = ? AND "
				+ KEY_SCORE + " < ?", new String[] { "" + score.getLevel(),
				"" + score.getScore() });
		db.close();	// close database
	}
}
